/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

 package br.ufpr.tads.ghtads.model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author VictorHugo
 */
 
public class ProfessorDao {
    private final Connection connection;
    
    private final String sqllist = "select * from pessoa";
    private final String sqllistone = "select * from professor where id = ?";
    private final String sqlInsertMateria = "insert into materia (nome,carga)values(?,?)";
    private final String sqlinsertEndereco = "insert into endereco (logradouro,numero,cep,bairro,cidade,uf)values(?,?,?,?,?,?)";
    private final String sqlInsertMateriaProfessor = "insert into pessoa_has_materia (materia_id,pessoa_id)values(?,?)";
    private final String sqlinsert = "insert into pessoa(nome,telefone,Endereco,Materiaprincipal,rg,senha,email,semanaRestricao,listamateria)values(?,?,?,?,?,?,?,?,?)";
    private final String sqlupdate = "";
    private final String sqldel = "";
    
    public ProfessorDao() throws IOException, FileNotFoundException, ClassNotFoundException, SQLException{
       this.connection = new ConnectionFactory().getConnection();
    }
    public void addProfessor(ProfessorDto professor) throws SQLException{
       connection.setAutoCommit(false);
       PreparedStatement stmt = connection.prepareStatement(sqlinsert,PreparedStatement.RETURN_GENERATED_KEYS);
       stmt.setString(1, professor.getNome());
       stmt.setInt(2, professor.getTelefone());
       stmt.setInt(3,this.gravaEndereco(professor));
       stmt.setInt(4, professor.getMateriaprincipal());
       stmt.setInt(5, professor.getRg());
       stmt.setString(6,professor.getSenha());
       stmt.setString(7,professor.getEmail());
       stmt.setString(8, professor.getSemanaRestricao());
       stmt.setString(9, professor.getListamateria());
       stmt.executeUpdate();
       professor.setId(this.lerId(stmt));
       this.listaMateteria(professor); 
       
       connection.commit();
    }
    private int gravaEndereco(ProfessorDto professor) throws SQLException{
       PreparedStatement stm = connection.prepareStatement(sqlinsertEndereco,PreparedStatement.RETURN_GENERATED_KEYS);
       stm.setString(1, professor.getEndereco().getLogradouro());
       stm.setInt(2, professor.getEndereco().getNumero());
       stm.setString(3, professor.getEndereco().getCep());
       stm.setString(4, professor.getEndereco().getBairro());
       stm.setString(5, professor.getEndereco().getCidade());
       stm.setString(6, professor.getEndereco().getUf());
       stm.execute();
       return this.lerId(stm);    
    }
    private int lerId(PreparedStatement stmt) throws SQLException {
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }
    private void listaMateteria(ProfessorDto professor) throws SQLException{
       List<MateriaDto> materias = professor.getMateria();
       for(MateriaDto materia: materias){   
           this.gravaProfessorMateria(professor.getId(), materia.getId());
        }    
    }
     private void gravaProfessorMateria(int idprofessor, int idmateria) throws SQLException{ 
         PreparedStatement stm = connection.prepareStatement(sqlInsertMateriaProfessor,PreparedStatement.RETURN_GENERATED_KEYS);
         stm.setInt(1,idmateria);
         stm.setInt(2,idprofessor);
         stm.execute();
     }
     public  List<ProfessorDto> listProfessor() throws SQLException{
         PreparedStatement stmt = connection.prepareStatement(sqllist);
         ResultSet rs = stmt.executeQuery();
         List<ProfessorDto> listProf = new ArrayList();
         while(rs.next()){
            ProfessorDto prof =  new ProfessorDto(); 
            prof.setNome(rs.getString("nome"));
            prof.setId(rs.getInt("id"));
            prof.setEmail(rs.getString("email"));
            prof.setMateriaprincipal(rs.getInt("materiaprincipal"));
            prof.setSemanaRestricao(rs.getString("semanaRestricao"));
            listProf.add(prof);
         }
         stmt.close();
         return listProf;
         
     }
}


